import argparse
import os
import pandas as pd


def get_excel_files(path):
    if path != '':
        files = list()
        for file in os.listdir(path):
            if os.path.splitext(file)[1] == '.xlsx' or os.path.splitext(file)[1] == '.xls':
                file_fullpath = os.path.join(path, file)
                files.append(file_fullpath)
        return files
    else:
        print('指定的文件路径错误...请检查并重新运行程序...')
        exit(1)


def show_files_name(file_names):
    files_name = ''
    count = len(file_names)
    i = 1
    for file_name in file_names:
        if i != count:
            files_name += '"%s",' % file_name
        else:
            files_name += '"%s"' % file_name
        i += 1

    return files_name


def execute_excel_files(file_names):
    check_file_results = dict()
    check_file_results['correct'] = list()
    check_file_results['incorrect'] = list()
    for file_name in file_names:
        try:
            detail = dict()

            data = pd.read_excel(file_name, engine='openpyxl', sheet_name='问题总览')
            data['数据坏点比例'] = data['数据坏点比例'].str.strip('%').astype(float) / 100
            data['坏点数'] = data['数据坏点比例'] * 390
            data['不可算点数'] = 390 - data['可计算点个数']
            data['坏点率'] = data['坏点数'] / data['不可算点数']
            data['可算点数不足主因'] = ''

            for i in data.index:
                if data['坏点率'].at[i] > 0.5:
                    data['可算点数不足主因'].at[i] = '坏点'
                else:
                    data['可算点数不足主因'].at[i] = '缺数'

            detail['all_tg_count'] = data.shape[0]
            detail['underload_tg_count'] = data.loc[data['台区状态'].apply(lambda status: status == '轻载')].shape[0]
            detail['overload_tg_count'] = data.loc[data['台区状态'].apply(lambda status: status == '重载')].shape[0]

            abnormal_tg = data.loc[data['台区状态'].apply(lambda status: status == '异常')]
            detail['abnormal_tg_count'] = abnormal_tg.shape[0]

            detail['having_no_summary_meter_count'] = abnormal_tg['无总表'].str.count('有').sum()
            detail['having_multi_summary_meter_count'] = abnormal_tg['多总表'].str.count('有').sum()
            detail['having_no_user_meter_count'] = abnormal_tg['无户表'].str.count('有').sum()
            detail['suspected_adjust_error_count'] = abnormal_tg['疑似户变关系错误'].str.count('有').sum()
            detail['having_no_data_count'] = abnormal_tg['全时段未上报'].str.count('有').sum()
            detail['abnormal_constant_count'] = abnormal_tg['用电量异常恒定'].str.count('有').sum()

            result_tg = abnormal_tg.loc[abnormal_tg['无总表'].apply(lambda x: x == '-')].loc[
                abnormal_tg['多总表'].apply(lambda x: x == '-')].loc[abnormal_tg['无户表'].apply(lambda x: x == '-')].loc[
                abnormal_tg['疑似户变关系错误'].apply(lambda x: x == '-')].loc[
                abnormal_tg['全时段未上报'].apply(lambda x: x == '-')].loc[
                abnormal_tg['用电量异常恒定'].apply(lambda x: x == '-')]

            detail['defective_pixel_count'] = result_tg['可算点数不足主因'].str.count('坏点').sum()
            detail['missing_pixel_count'] = result_tg['可算点数不足主因'].str.count('缺数').sum()

            txt_file_name = file_name + '_数据质量问题详情分布.txt'

            lines = list()
            lines.append('对应Excel文件：' + file_name)
            lines.append('\n\n台区总数量：' + str(detail['all_tg_count']))
            lines.append('\n异常台区数量：' + str(detail['abnormal_tg_count']))
            lines.append('\n\n无总表：' + str(detail['having_no_summary_meter_count']))
            lines.append('\n多总表：' + str(detail['having_multi_summary_meter_count']))
            lines.append('\n无户表：' + str(detail['having_no_user_meter_count']))
            lines.append('\n疑似户变关系错误：' + str(detail['suspected_adjust_error_count']))
            lines.append('\n全时段未上报：' + str(detail['having_no_data_count']))
            lines.append('\n用电量恒定异常' + str(detail['abnormal_constant_count']))
            lines.append('\n\n坏点：' + str(detail['defective_pixel_count']))
            lines.append('\n缺数：' + str(detail['missing_pixel_count']))
            lines.append('\n轻载台区数量：' + str(detail['underload_tg_count']))
            lines.append('\n重载台区数量：' + str(detail['overload_tg_count']) + '\n')

            with open(txt_file_name, 'w', encoding='utf-8') as file_object:
                for line in lines:
                    file_object.write(line)

            check_file_results['correct'].append(file_name)
        except KeyError:
            check_file_results['incorrect'].append(file_name)
    if len(show_files_name(check_file_results['incorrect'])) > 0:
        print('无法进行输出的文件名为:' + show_files_name(check_file_results['incorrect']) + '.请检查文件!!!')
    if len(show_files_name(check_file_results['correct'])) == 0:
        print('当前没有可以进行输出的文件...')
    elif len(show_files_name(check_file_results['correct'])) > 0:
        print('可以进行输出的文件名为:' + show_files_name(check_file_results['correct']) + '.输出结果完成...')
    print('程序运行结束...谢谢使用...再见!')


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='根据数据质量报告输出问题详情分布')
    parser.add_argument('-f', '--src_file', type=str, help='指定数据质量报告文件路径', default='')

    args = parser.parse_args()
    print('正在获取指定路径下的Excel文件...请稍候...')
    all_excel_files = get_excel_files(args.src_file)

    if len(all_excel_files) == 0:
        print('在指定的路径下未找到Excel文件...请检查路径并重试....')
    else:
        print('获取指定路径下的Excel文件成功...正在进行文件检查并进行结果输出...请稍候...')
        execute_excel_files(all_excel_files)
